package PhotoController;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

import Connect.connector;

public class PhotoOldDAO {
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	public ArrayList<PhotoOldBean> pt_old_list_main(){
		conn = connector.getConnection();
		ArrayList<PhotoOldBean> datas=new ArrayList<PhotoOldBean>();
		try{

			String sql;	
			
			sql = "select * from (select * from OLDPLUS order by id desc)a where a.id limit 0,4";

			pstmt = conn.prepareStatement(sql);
			
			rs=pstmt.executeQuery();
			while(rs.next()){
				PhotoOldBean photoOldBean=new PhotoOldBean();
				photoOldBean.setID(rs.getInt("id"));
				photoOldBean.setOld_Photo(rs.getString("Old_Photo"));
				System.out.println("aaa:"+rs.getString("Old_Photo"));
				datas.add(photoOldBean);
			}
			
			
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return datas;
	}
	
	public  ArrayList<PhotoOldBean> pt_old_list(HashMap param){
		conn = connector.getConnection();
		ArrayList<PhotoOldBean> datas=new ArrayList<PhotoOldBean>();
		try{
			
			String sql;	
			
			sql="select o.*,m.* from OLDPLUS o ,MEMBERS m where o.Members_Id=m.ID order by o.Old_Date limit ?,?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, ((Integer)param.get("startNum")).intValue());
			pstmt.setInt(2, ((Integer)param.get("pageSize")).intValue());
			rs=pstmt.executeQuery();
			while(rs.next()){

				PhotoOldBean photoOldBean=new PhotoOldBean();
				photoOldBean.setID(rs.getInt("id"));
				photoOldBean.setMembers_Id(rs.getInt("members_id"));
				photoOldBean.setOld_Add(rs.getString("old_add"));
				photoOldBean.setOld_Kind(rs.getString("old_kind"));
				photoOldBean.setOld_Gender(rs.getInt("old_gender"));
				photoOldBean.setOld_Money(rs.getString("old_money"));
				photoOldBean.setOld_Photo(rs.getString("old_photo"));
				photoOldBean.setOld_Date(rs.getDate("old_date"));
				photoOldBean.setMem_Nick(rs.getString("Mem_Nick"));
				photoOldBean.setMem_NameH(rs.getString("mem_nameh"));
				photoOldBean.setMem_NameK(rs.getString("mem_namek"));
				photoOldBean.setMem_Year(rs.getString("mem_year"));
				photoOldBean.setMem_Month(rs.getString("mem_month"));
				photoOldBean.setMem_Day(rs.getString("mem_day"));
				
				
				datas.add(photoOldBean);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
			
		}
		return datas;
	}
	
	public Integer pfTotalCount(HashMap param){
		conn = connector.getConnection();
		int count=0;
		try{
			
			String sql;	
			
			sql="select count(*) as cnt from OLDPLUS";
			
			pstmt = conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			
			if(rs.next()){
		    	count=rs.getInt("cnt");
		    	System.out.println(count);
		    }
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return count;
		
	}
	
	public ArrayList<POldCommentBean> pt_old_recom (int id){
		conn = connector.getConnection();
		ArrayList<POldCommentBean> datas=new ArrayList<POldCommentBean>();
		try{
			
			String sql;	
			
			sql = "select * from OLDCOMMENT where oldplus_id=? order by oc_date desc";

			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);

			rs=pstmt.executeQuery();
			while(rs.next()){
				POldCommentBean pOldCommentBean=new POldCommentBean();
				pOldCommentBean.setId(rs.getInt("id"));
				pOldCommentBean.setOldplus_id(rs.getInt("oldplus_id"));
				pOldCommentBean.setOc_nick(rs.getString("oc_nick"));
				pOldCommentBean.setOc_contents(rs.getString("oc_contents"));
				pOldCommentBean.setOc_date(rs.getDate("oc_date"));
				System.out.println("aa="+pOldCommentBean.getId());
				datas.add(pOldCommentBean);
			}
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return datas;
	}
	
	public ArrayList<POldCommentBean> pt_old_recom_write(int id,String oc_nick, String oc_contents){
		conn = connector.getConnection();
		ArrayList<POldCommentBean> datas=new ArrayList<POldCommentBean>();
		try{
			
			String sql;	
			String sql2;
			sql = "insert into OLDCOMMENT (id,oldplus_id,oc_nick,oc_contents,oc_date) values(null,?,?,?,Sysdate())";

			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			pstmt.setString(2, oc_nick);
			pstmt.setString(3, oc_contents);
			pstmt.executeUpdate();
			
			sql2 = "select * from OLDCOMMENT where oldplus_id=?";

			pstmt = conn.prepareStatement(sql2);
			pstmt.setInt(1, id);
			rs=pstmt.executeQuery();
			while(rs.next()){
				POldCommentBean pOldCommentBean=new POldCommentBean();
				pOldCommentBean.setId(rs.getInt("id"));
				pOldCommentBean.setOldplus_id(rs.getInt("oldplus_id"));
				pOldCommentBean.setOc_nick(rs.getString("oc_nick"));
				pOldCommentBean.setOc_contents(rs.getString("oc_contents"));
				pOldCommentBean.setOc_date(rs.getDate("oc_date"));
				datas.add(pOldCommentBean);
			}
			
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return datas;
	}
	
	public void pt_old_com_cancle(int com_id,int oldplus_id){
		conn = connector.getConnection();
		
		try{
			
			String sql;	
			sql = "delete from OLDCOMMENT where id=?";

			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, com_id);
			pstmt.executeUpdate();
			
			
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}
	
	public Integer young_level(String mem_nick){
		conn = connector.getConnection();
		int level=0;
		try{
			
			String sql;	
			sql = "select mem_level from MEMBERS where mem_nick=?";

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, mem_nick);
			rs=pstmt.executeQuery();
			if(rs.next()){
				level=rs.getInt("mem_level");
			}
			
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return level;
	}
}
